package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

public class DBTrainingProgram implements DBTrainingProgramIF
{
	private Connection con;
    
    public DBTrainingProgram() 
    {
      con = DBConnection.getInstance().getDBcon();
    }
    
    public ArrayList<TrainingProgram> getAllTrainingProgram(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }

    public TrainingProgram searchTrainingProgramID(int trainingProgramID, boolean retriveAssociation)
    {   
    	String wClause = "TrainingProgramID = " + trainingProgramID + "";
        return singleWhere(wClause, retriveAssociation);
    }

    public TrainingProgram searchTrainingProgramPmDate(String pmDate, boolean retriveAssociation)
    {   
    	String wClause = "pmDate = " + pmDate + "";
        return singleWhere(wClause, retriveAssociation);
    }
    
    public TrainingProgram searchTrainingProgramName(String name, boolean retriveAssociation)
    {
        String wClause = "Name  = " + name + "%'";
        System.out.println("Search TrainingProgram " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

    public TrainingProgram searchTrainingProgramCreatorName(String creatorName, boolean retriveAssociation)
    {
        String wClause = "CreatorName  = " + creatorName + "%'";
        System.out.println("Search TrainingProgram " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    public TrainingProgram searchTrainingProgramSupervisorID(int supervisorID, boolean retriveAssociation)
    {
        String wClause = "SupervisorID  = " + supervisorID + "%'";
        System.out.println("Search TrainingProgram " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    public int insertTrainingProgram(TrainingProgram tp)
    {
    	int nextId = GetMax.getMaxId("Select max(id) from TrainingProgram");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);
    	
       int rc = -1;
	   String query="INSERT INTO TrainingProgram(TrainingProgramID,	pmDate, Name, CreatorName, SupervisorID) VALUES(" +
	   		nextId  + ",'"  +
		    tp.getPmDate()  + "','" +
            tp.getName() + "'," +
            tp.getCreatorName() + "," +
            tp.getSupervisor().getSupervisorID() + ")";
                     
      System.out.println("insert : " + query);		  					
      try{
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in TrainingProgram db: "+ex);
	   }
	   return(rc);
	}
	
	public int updateTrainingProgram(TrainingProgram tp)
	{
		TrainingProgram tpObj  = tp;
		int rc=-1;
	  
		String query="UPDATE TrainingProgram SET " +
			" WHERE TrainingProgramID = '" + tpObj.getTrainingProgramID() + "'" +
		 	"pmDate ='" + tpObj.getPmDate() + "', " +
		 	"Name ='" + tpObj.getName() + "', " +
		 	"CreatorName ='" + tpObj.getCreatorName() + ", " +
		 	"SupervisorID =" + tpObj.getSupervisor().getSupervisorID();
			
            System.out.println("Update query:" + query);
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
	 	catch(Exception ex)
	 	{
	 	 	System.out.println("Update exception in TrainingProgram db: "+ex);
	  	}
		return(rc);
	}
	
	public int delete(int id)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM TrainingProgram WHERE id = " +
				id + "";
                System.out.println(query);
	  	try
	  	{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	    catch(Exception ex)
   	    {
	 	  	System.out.println("Delete exception in TrainingProgram db: "+ex);
   	    }
		return(rc);
	}
	
	private ArrayList<TrainingProgram> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<TrainingProgram> trainingProgramList = new ArrayList<TrainingProgram>();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			while( results.next() )
			{
				TrainingProgram tpObj = new TrainingProgram();
				tpObj = buildTrainingProgram(results);
				trainingProgramList.add(tpObj);
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return trainingProgramList;
	}
	
	private TrainingProgram singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		TrainingProgram tpObj = new TrainingProgram();
	    String query =  buildQuery(wClause);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			tpObj = buildTrainingProgram(results);
	 			stmt.close();
			}
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return tpObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM TrainingProgram";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;		
		return query;
	}

	private TrainingProgram buildTrainingProgram(ResultSet results)
      {   
		TrainingProgram tpObj = new TrainingProgram();
          try
          {
                tpObj.setTrainingProgramID(results.getInt("TrainingProgramID"));
                tpObj.setPmDate(results.getString("PMDate"));
                tpObj.setName(results.getString("Name"));
                tpObj.setCreatorName(results.getString("CreatorName"));
                tpObj.setSupervisor(new Supervisor(results.getInt("SupervisorID")));
          }
         catch(Exception e)
         {
             System.out.println("error in building the TrainingProgram object");
         }
         return tpObj;
      }

}
